package cn.oversky.eom;

import cn.oversky.eom.exception.EomException;
import cn.oversky.eom.exception.EomExceptionEnumImpl;
import cn.oversky.eom.rule.FormValid;
import cn.oversky.eom.rule.IFormValueRule;
import cn.oversky.eom.utils.ExcelBeansUtil;
import cn.oversky.eom.utils.PropertiesUtils;
import cn.oversky.eom.utils.ReflectionUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.parser.Feature;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.util.HtmlUtils;

import java.io.*;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

import static cn.oversky.eom.model.ThirdPartyConstant.DATE_REGEXP;
import static cn.oversky.eom.utils.PropertiesUtils.isNull;
import static cn.oversky.eom.utils.ReflectionUtils.newInstance;
import static cn.oversky.eom.utils.PropertiesUtils.isNotNull;

/**
 * @email CorvusY@foxmail.com
 * @date 2016/11/7 11:48.
 * Now is history !
 */
public class ExcelRowToObjectUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelRowToObjectUtil.class);

    public static final Pattern DATE_PATTERN = Pattern.compile(DATE_REGEXP);

    /**
     * @param sheetName sheet名称
     * @param start     选定区域的做上角单元格
     * @param end       选定区域的右下角单元格
     * @return 返回区域的引用公式
     * author :  CorvusY@foxmail.com 2017-6-19 21:36:38
     */
    public static String getRangeReference(String sheetName, Cell start, Cell end) {
        StringBuffer formulaBuffer = new StringBuffer(sheetName);
        formulaBuffer.append("!");
        formulaBuffer.append(absolutePosition(start.getAddress()));
        formulaBuffer.append(":");
        formulaBuffer.append(absolutePosition(end.getAddress()));
        System.out.println(formulaBuffer.toString());
        return formulaBuffer.toString();
    }

    private static String absolutePosition(CellAddress addr) {
        String colStr = CellReference.convertNumToColString(addr.getColumn());
        int rowStr = addr.getRow() + 1;
        return "$" + colStr + "$" + rowStr;
    }

    public static void main(String[] args) {
        char[] chars = "A1".toCharArray();
        char[] result = new char[4];
        result[3] = chars[1];
        result[1] = chars[0];
        result[2] = '$';
        result[0] = '$';
        chars[0] = '$';
        String s = JSON.toJSONString(result);
        System.out.println(s);
    }

    public static byte[] fillInBytes(List list, String json) throws NoSuchFieldException, IOException {
        return fillInBytes( list, jsonToEomapping( json ), "sheet1", "yyyy-MM-dd" );
    }
    public static byte[] fillInBytes(List list, String json, String sheetName, String pattern) throws NoSuchFieldException, IOException {
        return fillInBytes( list, jsonToEomapping( json ), sheetName, pattern );
    }

    public static byte[] fillInBytes(List list, Map<String, EOMapping> map, String sheetName, String pattern) throws NoSuchFieldException, IOException {
        Workbook wb = null;
        wb = new XSSFWorkbook();
        ExcelRowToObjectUtil.fillInWorkbook(list, map, sheetName, wb, pattern);

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        return os.toByteArray();
    }

    public static Workbook fillInWorkbook(List list, Map<String, EOMapping> map, String sheetName, Workbook wb, String pattern) throws NoSuchFieldException, IOException {
        int idx = 1;
        Sheet sheet = wb.createSheet(sheetName);
        // setFormula( map );
        fillInDictSheet(map, wb);
        setCellValidation(map, sheet);

        Row th = sheet.createRow(0);
        // 设置表头
        int[][] rowColumnsLength = new int[list.size() + 1][];
        rowColumnsLength[0] = fileInRow(null, th, map, true, pattern);
        for (Object o : list) {
            Row row = sheet.createRow(idx);
            // 填充 excel 具体数据
            rowColumnsLength[idx] = fileInRow(o, row, map, false, pattern);
            idx++;
        }

        // 设置没列的最大宽度
        int columnSize = map.size();
        int[] columnsMaxLength = columnsMaxLength(rowColumnsLength, columnSize);
        columnsAutoWidth(sheet, columnsMaxLength);

        // 美化第一行样式
        firstColumnBeautiful(sheet, wb);
        return wb;
    }

    public static Workbook fillInWorkbook(List list, Map<String, EOMapping> map, String sheetName, Workbook wb) throws NoSuchFieldException, IOException {
        return fillInWorkbook(list, map, sheetName, wb, "yyyy/MM//dd");
    }


    private static void firstColumnBeautiful(Sheet sheet, Workbook wb) {
        Row row = sheet.getRow(0);
        Iterator<Cell> cellIterator = row.cellIterator();
        CellStyle style = wb.createCellStyle();

        style.setFillBackgroundColor(HSSFColor.BLACK.index);
        style.setFillForegroundColor(HSSFColor.WHITE.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        sheet.setDefaultRowHeightInPoints(22);
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            cell.setCellStyle(style);
        }
    }

    private static int[] columnsMaxLength(int[][] rowColumnsLength, int columnSize) {
        int[] columnsMaxLength = new int[columnSize];
        for (int r = 0; r < rowColumnsLength.length; r++) {
            int[] rowsLength = rowColumnsLength[r];
            for (int c = 0; c < rowsLength.length; c++) {
                columnsMaxLength[c] = Math.max(rowsLength[c], columnsMaxLength[c]);
            }
        }
        return columnsMaxLength;
    }

    private static void columnsAutoWidth(Sheet sheet, int[] columnsMaxLength) {
        for (int c = 0; c < columnsMaxLength.length; c++) {
            int cl = columnsMaxLength[c];
            int columnLength = cl < 7 ? 7 : cl;
            sheet.setColumnWidth(c, Math.min(columnLength * 512 + 20, 2 << 15 - 1));
        }
    }

    public static int[] fileInRow(Object obj, Row row, Map<String, EOMapping> map, boolean th, String pattern) throws NoSuchFieldException {
        Set<String> keySet = map.keySet();
        int[] rowValuesLenth = new int[keySet.size()];
        int columnIndex = 0;
        for (String key : keySet) {
            EOMapping eoMapping = map.get(key);
            Cell cell = row.createCell(columnIndex);
            if (th) {
                cell.setCellValue(eoMapping.columnName);
                // 获取值的同时，回传宽度
                rowValuesLenth[columnIndex] = eoMapping.columnName == null ? 0 : eoMapping.columnName.length();
            } else {
                String fieldName = eoMapping.fieldName;
                Object o = null;
                if (obj instanceof Map) {
                    Map valueMap = (Map) obj;
                    o = valueMap.get(fieldName);
                } else {
                    o = ReflectionUtils.getFieldValue(obj, fieldName);
                }
                String cellValue;
                String patternDate = eoMapping.dateFormat == null ? pattern : eoMapping.dateFormat;
                cellValue = getCellValue(o, eoMapping.map, eoMapping.convert, patternDate);

                // 获取值的同时，回传宽度
                rowValuesLenth[columnIndex] = cellValue == null ? 0 : cellValue.length();

                cellValue = HtmlUtils.htmlUnescape( cellValue );
                if( isNotNull(eoMapping.valueFormat )) {
                    cellValue = eoMapping.valueFormat.format( obj, cellValue );
                }
                cell.setCellValue(cellValue);
            }
            columnIndex++;
        }
        return rowValuesLenth;
    }

    public static int[] fileInRow(Object obj, Row row, Map<String, EOMapping> map, boolean th) throws NoSuchFieldException {
        return fileInRow(obj, row, map, th, "yyyy/MM/dd");
    }

    private static void setFormula(Map<String, EOMapping> map) {
        Collection<EOMapping> eoms = map.values();
        List<EOMapping> dictEoms = new ArrayList<EOMapping>();
        int col = 0;
        for (EOMapping eom : eoms) {
            // 判断是否存在值转换映射
            if (eom.map != null) {
                eom.formula = "sys_dictionary_sheet!$2";
            }
            col++;
        }
    }

    /**
     * 创建 dict sheet 用于下拉框引用与单元格值校验
     *
     * @param map eomapping
     * @param wb  excel 文档文件
     */
    private static void fillInDictSheet(Map<String, EOMapping> map, Workbook wb) {
        Collection<EOMapping> eoms = map.values();
        List<EOMapping> dictEoms = new ArrayList<EOMapping>();
        for (EOMapping eom : eoms) {
            // 判断是否存在值转换映射
            if (eom.map != null) {
                dictEoms.add(eom);
            }
        }

        if (isNotNull(dictEoms)) {
            String dictSheetName = "sys_dictionary_sheet";
            Sheet dictSheet = wb.createSheet(dictSheetName);

            int c = 0;
            Cell[][] firstAndLast = new Cell[dictEoms.size()][];
            for (EOMapping dict : dictEoms) {
                // 创建当前字典所在行
                Map dictMap = dict.map;
                Set dictValue = dictMap.keySet();
                int r = 0;
                for (Object item : dictValue) {
                    Row row = dictSheet.getRow(r);
                    if (row == null) {
                        row = dictSheet.createRow(r);
                    }
                    Cell cell = row.createCell(c);
                    cell.setCellValue(item.toString());
                    if (r == 0) {
                        if (firstAndLast[c] == null)
                            firstAndLast[c] = new Cell[2];
                        firstAndLast[c][0] = cell;
                    } else if (r == dictMap.size() - 1) {
                        firstAndLast[c][1] = cell;
                    }
                    r++;
                }
                c++;
            }
            for (int col = 0; col < firstAndLast.length; col++) {
                if (firstAndLast[col] != null) {
                    dictEoms.get(col).formula = getRangeReference(dictSheetName, firstAndLast[col][0], firstAndLast[col][1]);
                }
            }
        }
    }

    private static XSSFWorkbook setCellValidation(String path, String sheetName, Map<String, EOMapping> map) {
        XSSFWorkbook wb = null;
        XSSFSheet sheetlist;

        try {
            File inputFile = new File(path);
            if (inputFile.exists()) {
                wb = new XSSFWorkbook(new FileInputStream(path));
            } else {
                wb = new XSSFWorkbook();// excel文件对象
            }
            if (wb.getSheet(sheetName) == null) {
                sheetlist = wb.createSheet(sheetName);// 工作表对象
            } else {
                sheetlist = wb.getSheet(sheetName);// 工作表对象
            }
            setCellValidation(map, sheetlist);
            FileOutputStream out = new FileOutputStream(path);
            wb.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return wb;
    }

    /**
     * 为单元格设置 下拉框
     *
     * @param eoMap
     * @param sheet
     */
    private static void setCellValidation(Map<String, EOMapping> eoMap, Sheet sheet) {
        int firstRow = 1;
        Collection<EOMapping> colMaps = eoMap.values();
        int currentCol = 0;
        // 遍历多个映射
        for (EOMapping colMap : colMaps) {
            String formula = colMap.formula;
            if (formula != null) {
                DataValidationHelper helper = sheet.getDataValidationHelper();

                //CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)设置行列范围
                CellRangeAddressList addressList = new CellRangeAddressList(firstRow, Integer.MAX_VALUE >> 11, currentCol, currentCol);

                //设置下拉框数据
                DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);
                DataValidation dataValidation = helper.createValidation(constraint, addressList);

                //处理Excel兼容性问题
                if (dataValidation instanceof XSSFDataValidation) {
                    dataValidation.setSuppressDropDownArrow(true);
                    dataValidation.setShowErrorBox(true);
                    dataValidation.setEmptyCellAllowed(false);
                } else {
                    dataValidation.setSuppressDropDownArrow(false);
                }

                sheet.addValidationData(dataValidation);
            }
            currentCol++;
        }

    }

    /**
     * @param list      存放 excel 行数据的空集合对象
     * @param excelRows excel 的行对象
     * @param map       映射关系
     * @param clz       转换的类
     * @throws Exception
     */
    public static void fillInList(List list, List<Row> excelRows, Map<String, EOMapping> map, Class clz, String pattern) {
        fillInList(list, excelRows, map, clz, 0, pattern);
    }
    public static void fillInList(List list, List<Row> excelRows, String json, Class clz, String pattern) {
        fillInList(list, excelRows, jsonToEomapping(json), clz, 0, pattern);
    }

    public static void fillInList(List list, List<Row> excelRows, Map<String, EOMapping> map, Class clz) {
        fillInList(list, excelRows, map, clz, "yyyy/MM/dd");
    }

    public static void fillInList(List list, List<Row> excelRows, String json, Class clz) {
        fillInList(list, excelRows, jsonToEomapping(json) , clz, "yyyy/MM/dd");
    }

    public static Map<String, EOMapping> jsonToEomapping(String str) {
        Map<String, EOMapping> map = new HashMap<String, EOMapping>();
        Map<String, HashMap> map2 = JSONObject.parseObject( str , LinkedHashMap.class, Feature.CustomMapDeserializer );
        for( Map.Entry<String, HashMap> me : map2.entrySet() ) {
            String k = me.getKey();
            HashMap v = me.getValue();
            EOMapping eoMapping = JSONObject.parseObject(JSONObject.toJSONString(v), EOMapping.class);
            map.put( k, eoMapping );
        }
        return map;
    }


    /**
     * @param list      存放 excel 行数据的空集合对象
     * @param excelRows excel 的行对象
     * @param map       映射关系
     * @param clz       转换的类
     * @param startRow  开始的行数
     * @throws Exception
     */
    public static void fillInList(List list, List<Row> excelRows, Map<String, EOMapping> map, Class clz, long startRow, String pattern) {
        fillInList(list, excelRows, map, clz, startRow, null, pattern);
    }

    public static void fillInList(List list, List<Row> excelRows, Map<String, EOMapping> map, Class clz, long startRow) {
        fillInList(list, excelRows, map, clz, startRow, "yyyy/MM/dd");
    }
    public static void fillInList(List list, List<Row> excelRows, String json,
                                  Class clz, long startRow, IExcelRowConfig thConvert, String pattern) {
        fillInList( list, excelRows, jsonToEomapping(json), clz, startRow, thConvert, pattern );
    }

    /**
     * @param list      存放 excel 行数据的空集合对象
     * @param excelRows excel 的行对象
     * @param map       映射关系
     * @param clz       转换的类
     * @param startRow  开始的行数
     * @throws Exception
     */
    public static void fillInList(List list, List<Row> excelRows, Map<String, EOMapping> map,
                                  Class clz, long startRow, IExcelRowConfig thConvert, String pattern) {
        excelRows = clearEmptyRow(excelRows);
        List tempList = new ArrayList();
        if (thConvert != null) {
            startRow = thConvert.convert(excelRows);
        }

        boolean first = true;
        Row frzrow = null;
        long l = 0;
        // 读取行数据，并填入两个对象
        for (Row row : excelRows) {
            if (l < startRow) {
                l++;
                continue;
            }
            if (first) {
                frzrow = row;
                first = false;
            } else {
                try {
                    checkTemplate( frzrow, map.values() );
                    // 创建空对象
                    Object emptyObj = newInstance(clz);
                    // 调用工具类，填充空对象
                    ExcelRowToObjectUtil.fillInObj(emptyObj, frzrow, row, map, thConvert, pattern);
                    tempList.add(emptyObj);
                } catch (EomException e) {
                    e.printStackTrace();
                    throw e;
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new EomException(EomExceptionEnumImpl.P999, e.getClass().getName() + " " + e.getMessage());
                }
            }
        }
        Map ruleMap = EOMapping.getRuleMap(map);
        Set set = null;
        try {
            set = FormValid.doRule(tempList, ruleMap);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        if (set.size() > 0) {
            long offset = thConvert == null ? 1L : thConvert.getThRows();
            offset++; // 将下标调整未 excel 行所对应的下标。即变更起始值为 1；
            throw new EomException(EomExceptionEnumImpl.B248, join(set, "；", map, offset));
        }
        ReflectionUtils.removeEmptyList(tempList);

        list.addAll(tempList);
    }

    private static void checkTemplate(Row frzrow, Collection<EOMapping> values) {
        Iterator<Cell> cellIterator = frzrow.cellIterator();

        Set<String> columnNameSet = new HashSet<String>();

        while (cellIterator.hasNext()) {
            Cell next = cellIterator.next();
            String stringCellValue = next.getStringCellValue();
            columnNameSet.add( stringCellValue );
        }

        for( EOMapping eoMapping : values ) {
            String columnName = eoMapping.columnName;
            boolean contains = columnNameSet.contains(columnName);
            if( !contains && eoMapping.isThRequired() ) {
                throw new EomException(EomExceptionEnumImpl.B603, columnName);
            }
        }
    }

    private static String join(Set<FormValid.ErrorMessage> errorMessages, String sep, Map<String, EOMapping> map, long offset) {
        StringBuffer sb = new StringBuffer();
        for (FormValid.ErrorMessage errorMessage : errorMessages) {
            sb.append("<br>");
            sb.append("- ");
            sb.append(errorMessage.getMessage());
            sb.append("：");
            sb.append("第 ");
            sb.append(errorMessage.getY() + offset);
            sb.append(" 行，");
            sb.append(map.get(errorMessage.getField()).getColumnName());
            sb.append(sep);
        }
        return sb.toString();
    }

    public static void fillInList(List list, List<Row> excelRows, Map<String, EOMapping> map, Class clz, long startRow, IExcelRowConfig thConvert) {
        fillInList(list, excelRows, map, clz, startRow, thConvert, "yyyy/MM/dd");
    }

    private static List<Row> clearEmptyRow(List<Row> excelRows) {
        List<Row> newRows = new ArrayList<Row>();
        Iterator<Row> iterator = excelRows.iterator();
        while (iterator.hasNext()) {
            Row row = iterator.next();
            short lastCellNum = row.getLastCellNum();
            Cell lastCell = row.getCell(lastCellNum - 1);
            if (lastCellNum != 1) {
                newRows.add(row);
            } else if (lastCell != null && !"".equals(lastCell.toString())) {
                newRows.add(row);
            }
        }
        return newRows;
    }

    /**
     * 根据 excel 数据填充对象
     *
     * @param t      数据空对象
     * @param frzRow 表头
     * @param row    数据行
     * @param map    列与字段的映射
     * @throws Exception
     */
    public static void fillInObj(Object t, Row frzRow, Row row, Map<String, EOMapping> map, String pattern) throws Exception {
        fillInObj(t, frzRow, row, map, null, pattern);
    }

    public static void fillInObj(Object t, Row frzRow, Row row, Map<String, EOMapping> map) throws Exception {
        fillInObj(t, frzRow, row, map, "yyyy/MM/dd");
    }

    /**
     * 根据 excel 数据填充对象
     *
     * @param t         数据空对象
     * @param frzRow    表头
     * @param row       数据行
     * @param map       列与字段的映射
     * @param thConvert Excel 头部转换器
     * @throws Exception
     */
    public static void fillInObj(Object t, Row frzRow, Row row, Map<String, EOMapping> map, IExcelRowConfig thConvert, String pattern) throws Exception {
        Set<String> keySet = map.keySet();

        for (String key : keySet) {
            EOMapping eom = map.get(key);
            // 获取映射信息
            String columnName = eom.columnName;
            String fieldName = eom.fieldName;

            // 获取对象类型、属性类型
            ExcelBeansUtil.LeafValueSetter leafValueSetter = ExcelBeansUtil.fitOut(t, fieldName, thConvert);

            if (eom.fieldValue != null) {
                leafValueSetter.set(eom.fieldValue);
                continue;
            }

            // 设置访问权限、并修改属性值
            Object value;
            // 如果字段给了默认值，则不从 excel 中获取
            // 获取字段值所在列
            int i = getCellIndex(frzRow, columnName);
            if (i == -1) {
                if (columnName.contains(".")) {
                    continue;
                } else {
                    throw new EomException(EomExceptionEnumImpl.B243, columnName);
                }
            }
            Object cellValue = getCellValue(row.getCell(i), leafValueSetter.getFieldType(), eom, pattern);
            value = cellValue;

            if (isNull(value) && isNotNull(eom.defaultValue)) {
                leafValueSetter.set(eom.defaultValue);
            } else {
                leafValueSetter.set(value);
            }
        }
    }

    public static void fillInObj(Object t, Row frzRow, Row row, Map<String, EOMapping> map, IExcelRowConfig thConvert) throws Exception {
        fillInObj(t, frzRow, row, map, thConvert, "yyyy/MM/dd");
    }

    /**
     * 通过表头获取列的索引（第几列）
     *
     * @param frzRow     表头行
     * @param columnName 列的表头名
     * @return
     */
    private static int getCellIndex(Row frzRow, String columnName) {
        // 获取 单元格迭代器，列寻址
        Iterator<Cell> cellIterator = frzRow.cellIterator();
        int idx = 0;
        while (cellIterator.hasNext()) {
            Cell next = cellIterator.next();
            String stringCellValue = next.getStringCellValue();
            if (columnName.equals(stringCellValue)) {
                return idx;
            }
            idx++;
        }
        return -1;
    }

    private static String getCellValue(Object o, Map<String, Object> map, EOMFieldConvert convert, String pattern) {
        if (map != null) {
            for (String key : map.keySet()) {
                Object o1 = map.get(key);
                if (o1 != null && o1.equals(o)) {
                    return key;
                } else if (o != null && o1.equals(o.toString())) {
                    return key;
                }
            }
        }
        if (convert != null) {
            if (isNull(o)) return null;
            return (String) convert.doRevert((o instanceof BigDecimal) ? ((BigDecimal) o).longValue() : o);
        }
        return getCellValue(o, pattern);
    }

    private static String getCellValue(Object o, Map<String, Object> map, EOMFieldConvert convert) {
        return getCellValue(o, map, convert, "yyyy/MM/dd");
    }

    private static String getCellValue(Object obj, String pattern) {
        return
                isNull(obj) ? ""
                        : obj instanceof Date ? new SimpleDateFormat(pattern).format(obj)
                        : obj instanceof Number ? obj.toString()
                        : obj instanceof String ? obj.toString()
                        : obj.toString();
    }

    private static String getCellValue(Object obj) {
        return getCellValue(obj, "yyyy/MM/dd");
    }

    /**
     * 先将 excel 多种类型的值转换成 string 类型
     *
     * @param cell 单元格对象
     * @return
     * @see #getCellValue(org.apache.poi.ss.usermodel.Cell, java.lang.reflect.Type)
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";
        String cellValue;
        DecimalFormat df = new DecimalFormat("#.###");
        // 获取单元格类型，并根据单元格获取值
        int cellType = cell.getCellType();
        cellValue =
                cellType == Cell.CELL_TYPE_STRING ? cell.getRichStringCellValue().getString().trim()                            // String cell value --> String
                        : cellType == Cell.CELL_TYPE_NUMERIC ? df.format(cell.getNumericCellValue()).toString()           // Numeric cell value --> String
                        : cellType == Cell.CELL_TYPE_BOOLEAN ? String.valueOf(cell.getBooleanCellValue()).trim()         // Boolean cell value --> String
                        : cellType == Cell.CELL_TYPE_FORMULA ? String.valueOf(cell.getRichStringCellValue())            // 公式 --> String
                        : "";
        cellValue = HtmlUtils.htmlUnescape(cellValue);
        cellValue = PropertiesUtils.isNotNull(cellValue) ? StringUtils.stripToEmpty(cellValue) : "";
        return cellValue;
    }

    private static Object getCellValue(Cell cell, Type fieldType, String pattern) throws Exception {
        return getCellValue(cell, fieldType, null, pattern);
    }

    private static Object getCellValue(Cell cell, Type fieldType) throws Exception {
        return getCellValue(cell, fieldType, "yyyy/MM/dd");
    }

    /**
     * 将单元格的值转换成指定类型
     *
     * @param cell      单元格对象
     * @param fieldType 实体类属性类型
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws java.text.ParseException
     */
    private static Object getCellValue(Cell cell, Type fieldType, EOMapping eom, String pattern) {
        if (eom.fieldValue != null) return eom.fieldValue;
        Map map = eom.map;
        EOMFieldConvert convert = eom.convert;
        IFormValueRule rule = eom.rule;
        Object ignoreValue = eom.ignoreValue;
        Class forceType = eom.forceType;

        Object result = null;
        String cellLocation;
        if (isNotNull(cell) && isNotNull(cell.getAddress()) && isNotNull(cell.getAddress().getRow())) {
            int row = cell.getAddress().getRow() + 1; // 1 纠正程序起始值，与生活中数字起始值的差异
            cellLocation = " 第 " + row + " 行";
        } else {
            cellLocation = "";
        }
        try {
            // 再转换成指定的类型，以减少多层条件分支 ----- ！！性能有部分损耗！！
            String cellValue = getCellValue(cell);
            if (ignoreValue != null && ignoreValue.equals(cellValue)) {
                return null;
            }
            // excel 值校验
            if (rule != null) {
                rule.doRule(cellValue, cellLocation);
            }
            if (isNull(cellValue)) {
                return null;
            }


            // 字典转化
            if (map != null) {
                Object o = map.get(cellValue);
                cellValue = o == null ? cellValue : o.toString();
            }
            // 转换器转化
            if (convert != null) {
                return convert.doConvert(cellValue);
            }
            // 如果数字为空值，则返回空对象，不再进行解析
            if ("".equals(cellValue)) return null;

            if ((forceType == Date.class || fieldType == Date.class) && DATE_PATTERN.matcher(cellValue).matches()) {
                try {
                    result = new SimpleDateFormat(pattern).parse(cellValue);              // Cell --> Date
                } catch (ParseException e) {
                    result = new SimpleDateFormat("yyyy-MM-dd").parse(cellValue);              // Cell --> Date
                }
                if (fieldType == String.class) {
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    result = sdf.format(result);
                }
            } else {
                result = fieldType == Long.class ? Long.parseLong(cellValue)                        // String --> Long
                        : fieldType == Integer.class ? Integer.parseInt(cellValue)                            // String --> Integer
                        : fieldType == Short.class ? Short.parseShort(cellValue)                         // String --> Short
                        : fieldType == BigDecimal.class ? new BigDecimal(cellValue)               // String --> BigDecimal
                        : fieldType == Date.class ? (
                        DATE_PATTERN.matcher(cellValue).matches()
                                ? new SimpleDateFormat(pattern).parse(cellValue)
                                : cell.getDateCellValue()                                                                  // Cell --> Date
                )
                        : cellValue;
            }
        } catch (ParseException e) {
            e.printStackTrace();
            throw new EomException(EomExceptionEnumImpl.B601, "日期", "格式要求：" + pattern + " 或 yyyy-MM-dd " + cellLocation);
        } catch (NumberFormatException e) {
            e.printStackTrace();
            throw new EomException(EomExceptionEnumImpl.B601, "数字", e.getMessage() + cellLocation);
        } catch ( IllegalStateException e ) {
            throw new EomException(EomExceptionEnumImpl.B601, eom.columnName, cellLocation + " " + cell.toString() );
        }
        return result;
    }

    private static Object getCellValue(Cell cell, Type fieldType, EOMapping eom) {
        return getCellValue(cell, fieldType, eom, "yyyy/MM/dd");
    }

}
